from flask import Blueprint, request, render_template
import time
from .admin import login_required
from .db import get_db

report = Blueprint("report", __name__)

@report.route("/report")
@login_required
def do_report():

    conn = get_db()
    cur = conn.cursor()
    
    t = request.values.get("time","")
    if not t:
        start = time.strftime("%Y-%m-%d %H:00:00", time.localtime(time.time() - 24 * 60 * 60))
        end = time.strftime("%Y-%m-%d %H:%M:%S")
        n_date = time.strftime("%Y-%m-%d")
    else:
        end = t
        t1 = time.strptime(t, "%Y-%m-%d %H:%M:%S")
        start = time.strftime("%Y-%m-%d %H:00:00", time.localtime(time.mktime(t1) - 24 * 60 * 60))
        n_date = time.strftime("%Y-%m-%d", time.localtime(time.mktime(t1)))

    sql = "select count(distinct terminalid) from userinfo where createdate > '%s' and  createdate < '%s'" % (start, end)
    cur.execute(sql)
    rs = cur.fetchall()
    new_user = int(rs[0][0])

    sql = "select count(distinct terminalid) from mediatimes where last_update > '%s' and  last_update < '%s'" % (start, end)
    cur.execute(sql)
    rs = cur.fetchall()
    active_user = int(rs[0][0])

    sql = "select count(distinct terminalid) from mediatimes where videotime > 0 and last_update > '%s' and  last_update < '%s'" % (start, end)
    cur.execute(sql)
    rs = cur.fetchall()
    video_user = int(rs[0][0])

    sql = "select count(distinct terminalid) from mediatimes where musictime > 0 and last_update > '%s' and  last_update < '%s'" % (start, end)
    cur.execute(sql)
    rs = cur.fetchall()
    music_user = int(rs[0][0])

    sql = "select count(distinct terminalid) from mediatimes where picturetime > 0 and last_update > '%s' and  last_update < '%s'" % (start, end)
    cur.execute(sql)
    rs = cur.fetchall()
    picture_user = int(rs[0][0])

    sql = "select count(distinct terminalid) from mediatimes where documenttime > 0 and last_update > '%s' and  last_update < '%s'" % (start, end)
    cur.execute(sql)
    rs = cur.fetchall()
    document_user = int(rs[0][0])

    sql = "select count(distinct terminalid) from mediatimes where baiduliveness > 0 and last_update > '%s' and  last_update < '%s'" % (start, end)
    cur.execute(sql)
    rs = cur.fetchall()
    baidu_user = int(rs[0][0])

    sql = "select sum(videotime),sum(musictime),sum(documenttime),sum(picturetime),sum(startcount) from mediatimes where last_update > '%s' and  last_update < '%s'" % (start, end)
    cur.execute(sql)
    rs=cur.fetchall()
    videotime,musictime,doctime,picturetime,startcount = rs[0]

    sql = "select count(distinct terminalid) from userinfo where createdate < '%s'" % end
    cur.execute(sql)
    rs=cur.fetchall()
    total_client = rs[0][0]

    sql = "select count(distinct deviceid) from userinfo where createdate < '%s'" % end
    cur.execute(sql)
    rs=cur.fetchall()
    total_dev = rs[0][0]

    sql = "select videoname,count(videoname) as c from movienames where watch_time > '%s' and watch_time < '%s' group by videoname order by c desc limit 10" % (start, end)
    cur.execute(sql)
    rs = cur.fetchall()
    today_favorite_mov =[ (m,t) for (m,t) in rs ]

    sql = "select videoname,count(videoname) as c from movienames where watch_time < '%s' group by videoname order by c desc limit 10" %  (end, )
    cur.execute(sql)
    rs = cur.fetchall()
    total_favorite_mov =[ (m,t) for (m,t) in rs ]

    sql = """select 
        userinfo.terminaltype,count(userinfo.terminaltype) as t, terminalname.friendlyname 
        from userinfo left join terminalname on userinfo.terminaltype = terminalname.terminalname 
        where createdate < '%s' 
        group by userinfo.terminaltype 
        order by t desc
        limit 10
        """ % (end, )
    cur.execute(sql)
    rs = cur.fetchall()
    favorite_dev = [ (m, t, t1) for (m, t, t1) in rs ]

    #sql = """select videoname,duration from movienames where watch_time < '%s' order by duration desc limit 10""" % (end, )
    #cur.execute(sql)
    #rs = cur.fetchall()
    #video_watch  = [ (m, t) for (m, t) in rs ]

    cur.close()

    #tpl = os.path.join(os.path.dirname(__file__), "status_rpt.html")
    return render_template("status_rpt.html", **locals())
